#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import datetime, json
import requests
import pandas as pd
import numpy as np

from xpy3lib.XLogger import XLogger

from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave

from sicost.AbstractDPJob import AbstractDPJob
from sicost.service.RequestMatWTAPI import RequestMatWTAPI


class DP0102Job(AbstractDPJob):
    """

    """
    WT_INTERFACE = 'http://190.2.245.29/BGM2M1/api/CallCpp'

    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_unit=None,
                 p_account=None,
                 p_cost_center=None,
                 p_account_period_start=None,
                 p_account_period_end=None,
                 p_data_type=None):
        super(DP0102Job, self).__init__(p_config=p_config,
                                        p_db_conn_mpp=p_db_conn_mpp,
                                        p_db_conn_rds=p_db_conn_rds,
                                        p_db_conn_dbprod7=p_db_conn_dbprod7,
                                        p_unit=p_unit,
                                        p_account=p_account,
                                        p_cost_center=p_cost_center,
                                        p_account_period_start=p_account_period_start,
                                        p_account_period_end=p_account_period_end)

        self.data_type = p_data_type

    def execute(self):
        return self.do_execute()



    def do_execute(self):
        self.logger.info('DP0102Job.do_execute')

        """
        1001  MIHB  和两个时间是传进去的
        其他的是写死的
        
        1001是传进去的
        P_ACCOUNT
        P_COST_CENTER
        V_ACCOUNT_PERIOD_START
        P_ACCOUNT_PERIOD_END

        涉及分卷的开始时间-6小时
        SET V_ACCOUNT_PERIOD_START = TO_CHAR(to_timestamp(P_ACCOUNT_PERIOD_START,'YYYYMMDDHH24MISS')- 6 HOURS,'YYYYMMDDHH24MISS');


        IN P_ACCOUNT	VARCHAR(8),
        IN P_COST_CENTER	VARCHAR(4),
        IN P_UNIT	VARCHAR(8),
        IN P_ACCOUNT_PERIOD_START	VARCHAR(14),
        IN P_ACCOUNT_PERIOD_END	VARCHAR(14) )
        这个DP0102会传入5个变量进去

        """
        code = 0
        p_app_throw_ai_mode = '%PN%'
        if self.data_type == 'M':
            t = datetime.datetime.strptime(self.account_period_start, '%Y%m%tmp_dict%H%M%S')
            for i in range(6):
                t -= datetime.timedelta(hours=1)
            v_account_period_start = t.strftime('%Y%m%tmp_dict%H%M%S')
            sql = "SELECT " \
                  " HEX(RAND())||TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_ID, " \
                  " ACCOUNT,SHIFT,TURN as TEAM, " \
                  " LEFT(TRIM(M_CENT),2) AS FACTORY, " \
                  " '%s' AS UNIT, " \
                  " LEFT(M_CENT,4) as COST_CENTER, " \
                  " TRIM(app_throw_ai_date||app_throw_ai_t) AS WORK_TIME, " \
                  " TRIM(app_throw_ai_date||app_throw_ai_t) AS PROCESS_START_TIME, " \
                  " TRIM(app_throw_ai_date||app_throw_ai_t) AS PROCESS_END_TIME, " \
                  " PRODUCT_CODE, " \
                  " ST_NO, " \
                  " MAT_NO, " \
                  " DEVO_PRODUCT_CODE AS IN_PRODUCT_CODE, " \
                  " IN_MAT_NO, " \
                  " VAL_Z as WT, " \
                  " VAL_Z AS ACT_WT, " \
                  " VAL_M AS IN_WT, " \
                  " VAL_M AS ACT_IN_WT, " \
                  " APP_THROW_AI_MODE, " \
                  " DESIGN_ANNEAL_DIAGRAM_CODE, " \
                  " TRIM_FLAG, " \
                  " MAT_ACT_WIDTH, " \
                  " MAT_ACT_THICK, " \
                  " IN_MAT_WIDTH, " \
                  " IN_MAT_THICK, " \
                  " PLAN_NO, " \
                  " TRIM_WIDTH, " \
                  " IN_MAT_INNER_DIA, " \
                  " CUST_ORDER_NO, " \
                  " PICKL_TRIM_FLAG, " \
                  " SORT_GRADE_CODE, " \
                  " LAYER_TYPE, " \
                  " TOP_COAT_WT, " \
                  " BOT_COAT_WT, " \
                  " 0 as MAT_ACT_AREA, " \
                  " 'M' as DATA_TYPE, " \
                  " LAS_NOTCH_FLAG, " \
                  " 'BGRAGGCB' AS REC_REVISOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME," \
                  " 'BGRAGGCB' AS REC_CREATOR," \
                  " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME" \
                  " FROM M1_JH.SU_JHBG_DMAMI1 " \
                  " WHERE 1=1 " \
                  " AND ACCOUNT='%s' " \
                  " AND COST_CENTER='%s' " \
                  " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)>='%s'" \
                  " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)<'%s'" \
                  " AND APP_THROW_AI_MODE LIKE '%s' " \
                  " AND MAT_ACT_WT!=0 " \
                  " AND ACCOUNT_TITLE_ITEM='01' " % (self.unit,
                                                     self.account,
                                                     self.cost_center,
                                                     v_account_period_start,
                                                     self.account_period_end,
                                                     p_app_throw_ai_mode)
            self.logger.info(sql)
            dfM1 = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
            success = dfM1.empty is False
            if success is True:
                dfM1.columns = dfM1.columns.str.upper()
                dfM1['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
                # dfM1['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
                dfM1['TOP_COAT_WT'].fillna(value=0, inplace=True)
                dfM1['BOT_COAT_WT'].fillna(value=0, inplace=True)
                success, dfM2 = self.__step_2()
                if success is False:
                    return code

                success, dfM3 = self.__step_3(p_df1=dfM1, p_df2=dfM2)
                if success is False:
                    return code
                # dfM5 = self.__step_5(p_df5=dfM3)

                # dfM5_new, df6 = self.__step_5_1(df5=dfM5)

                sql = " DELETE FROM BGTARAS1.T_ADS_FACT_SICB_DP0102 " \
                      " WHERE 1=1 " \
                      " AND LEFT(PRODUCE_TIME,14) >= '%s' " \
                      " AND LEFT(PRODUCE_TIME,14) < '%s' " \
                      " AND ACCT = '%s' " \
                      " AND COST_CENTER = '%s' " \
                      " AND UNIT_CODE='%s' " \
                      " AND DATA_TYPE = '%s'" % (
                          self.account_period_start, self.account_period_end, self.account, self.cost_center, self.unit,
                          self.data_type)
                self.logger.info(sql)
                self.db_conn_rds.execute(sql)
                code = self.__step_8(df54=dfM3)
                sql = "SELECT " \
                      " HEX(RAND())||TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_ID, " \
                      " ACCOUNT,SHIFT,TURN as TEAM, " \
                      " LEFT(TRIM(M_CENT),2) AS FACTORY, " \
                      " '%s' AS UNIT, " \
                      " LEFT(M_CENT,4) as COST_CENTER, " \
                      " TRIM(app_throw_ai_date||app_throw_ai_t) AS WORK_TIME, " \
                      " TRIM(app_throw_ai_date||app_throw_ai_t) AS PROCESS_START_TIME, " \
                      " TRIM(app_throw_ai_date||app_throw_ai_t) AS PROCESS_END_TIME, " \
                      " PRODUCT_CODE, " \
                      " ST_NO, " \
                      " MAT_NO, " \
                      " DEVO_PRODUCT_CODE AS IN_PRODUCT_CODE, " \
                      " IN_MAT_NO, " \
                      " VAL_Z as WT, " \
                      " VAL_Z AS ACT_WT, " \
                      " VAL_M AS IN_WT, " \
                      " VAL_M AS ACT_IN_WT, " \
                      " APP_THROW_AI_MODE, " \
                      " DESIGN_ANNEAL_DIAGRAM_CODE, " \
                      " TRIM_FLAG, " \
                      " MAT_ACT_WIDTH, " \
                      " MAT_ACT_THICK, " \
                      " IN_MAT_WIDTH, " \
                      " IN_MAT_THICK, " \
                      " PLAN_NO, " \
                      " TRIM_WIDTH, " \
                      " IN_MAT_INNER_DIA, " \
                      " CUST_ORDER_NO, " \
                      " PICKL_TRIM_FLAG, " \
                      " SORT_GRADE_CODE, " \
                      " LAYER_TYPE, " \
                      " TOP_COAT_WT, " \
                      " BOT_COAT_WT, " \
                      " 0 as MAT_ACT_AREA, " \
                      " 'M' as DATA_TYPE, " \
                      " LAS_NOTCH_FLAG, " \
                      " 'BGRAGGCB' AS REC_REVISOR," \
                      " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME," \
                      " 'BGRAGGCB' AS REC_CREATOR," \
                      " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME" \
                      " FROM M1_JH.SU_JHBG_DMAMI1 " \
                      " WHERE 1=1 " \
                      " AND ACCOUNT='%s' " \
                      " AND COST_CENTER='%s' " \
                      " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)>='%s'" \
                      " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)<'%s'" \
                      " AND APP_THROW_AI_MODE NOT LIKE '%s' " \
                      " AND MAT_ACT_WT!=0 " \
                      " AND ACCOUNT_TITLE_ITEM='01' " % (self.unit,
                                                         self.account,
                                                         self.cost_center,
                                                         v_account_period_start,
                                                         self.account_period_end,
                                                         p_app_throw_ai_mode)
                self.logger.info(sql)
                dfM7 = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
                success = dfM7.empty is False
                if success is True:
                    dfM7.columns = dfM7.columns.str.upper()
                    dfM7['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
                    # dfM7['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
                    dfM7['TOP_COAT_WT'].fillna(value=0, inplace=True)
                    dfM7['BOT_COAT_WT'].fillna(value=0, inplace=True)
                    success, dfM8 = self.__step_2()
                    if success is False:
                        return code

                    success, dfM9 = self.__step_3(p_df1=dfM7, p_df2=dfM8)
                    if success is False:
                        return code
                    code = self.__step_8(df54=dfM9)
            else:
                sql = "SELECT " \
                      " HEX(RAND())||TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_ID, " \
                      " ACCOUNT,SHIFT,TURN as TEAM, " \
                      " LEFT(TRIM(M_CENT),2) AS FACTORY, " \
                      " '%s' AS UNIT, " \
                      " LEFT(M_CENT,4) as COST_CENTER, " \
                      " TRIM(app_throw_ai_date||app_throw_ai_t) AS WORK_TIME, " \
                      " TRIM(app_throw_ai_date||app_throw_ai_t) AS PROCESS_START_TIME, " \
                      " TRIM(app_throw_ai_date||app_throw_ai_t) AS PROCESS_END_TIME, " \
                      " PRODUCT_CODE, " \
                      " ST_NO, " \
                      " MAT_NO, " \
                      " DEVO_PRODUCT_CODE AS IN_PRODUCT_CODE, " \
                      " IN_MAT_NO, " \
                      " VAL_Z as WT, " \
                      " VAL_Z AS ACT_WT, " \
                      " VAL_M AS IN_WT, " \
                      " VAL_M AS ACT_IN_WT, " \
                      " APP_THROW_AI_MODE, " \
                      " DESIGN_ANNEAL_DIAGRAM_CODE, " \
                      " TRIM_FLAG, " \
                      " MAT_ACT_WIDTH, " \
                      " MAT_ACT_THICK, " \
                      " IN_MAT_WIDTH, " \
                      " IN_MAT_THICK, " \
                      " PLAN_NO, " \
                      " TRIM_WIDTH, " \
                      " IN_MAT_INNER_DIA, " \
                      " CUST_ORDER_NO, " \
                      " PICKL_TRIM_FLAG, " \
                      " SORT_GRADE_CODE, " \
                      " LAYER_TYPE, " \
                      " TOP_COAT_WT, " \
                      " BOT_COAT_WT, " \
                      " 0 as MAT_ACT_AREA, " \
                      " 'M' as DATA_TYPE, " \
                      " LAS_NOTCH_FLAG, " \
                      " 'BGRAGGCB' AS REC_REVISOR," \
                      " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME," \
                      " 'BGRAGGCB' AS REC_CREATOR," \
                      " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME" \
                      " FROM M1_JH.SU_JHBG_DMAMI1 " \
                      " WHERE 1=1 " \
                      " AND ACCOUNT='%s' " \
                      " AND COST_CENTER='%s' " \
                      " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)>='%s'" \
                      " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)<'%s'" \
                      " AND APP_THROW_AI_MODE NOT LIKE '%s' " \
                      " AND MAT_ACT_WT!=0 " \
                      " AND ACCOUNT_TITLE_ITEM='01' " % (self.unit,
                                                         self.account,
                                                         self.cost_center,
                                                         v_account_period_start,
                                                         self.account_period_end,
                                                         p_app_throw_ai_mode)
                self.logger.info(sql)
                dfM7 = XRetryableQuery(p_db_conn=self.db_conn_mpp, p_sql=sql, p_max_times=5).redo()
                success = dfM7.empty is False
                if success is True:
                    dfM7.columns = dfM7.columns.str.upper()
                    dfM7['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
                    # dfM7['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
                    dfM7['TOP_COAT_WT'].fillna(value=0, inplace=True)
                    dfM7['BOT_COAT_WT'].fillna(value=0, inplace=True)
                    success, dfM8 = self.__step_2()
                    if success is False:
                        return code

                    success, dfM9 = self.__step_3(p_df1=dfM7, p_df2=dfM8)
                    if success is False:
                        return code
                    sql = " DELETE FROM BGTARAS1.T_ADS_FACT_SICB_DP0102 " \
                          " WHERE 1=1 " \
                          " AND LEFT(PRODUCE_TIME,14) >= '%s' " \
                          " AND LEFT(PRODUCE_TIME,14) < '%s' " \
                          " AND ACCT = '%s' " \
                          " AND COST_CENTER = '%s' " \
                          " AND UNIT_CODE='%s' " \
                          " AND DATA_TYPE = '%s'" % (
                              self.account_period_start, self.account_period_end, self.account, self.cost_center,
                              self.unit,
                              self.data_type)
                    self.logger.info(sql)
                    self.db_conn_rds.execute(sql)
                    code = self.__step_8(df54=dfM9)

        else:
            success, df1 = self.__step_1(p_db_conn_dbprod7=self.db_conn_dbprod7,
                                         p_app_throw_ai_mode=p_app_throw_ai_mode, p_db_conn_mpp=self.db_conn_mpp)
            if success is True:

                df1.columns = df1.columns.str.upper()
                df1['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
                # df1['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
                df1['TOP_COAT_WT'].fillna(value=0, inplace=True)
                df1['BOT_COAT_WT'].fillna(value=0, inplace=True)
                # df1['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
                # df1['PICKL_TRIM_FLAG'] = ''
                # df1['IN_MAT_INNER_DIA'] = 0

                # df1['IN_MAT_INNER_DIA'].fillna(0)
                # df1['TOP_COAT_WT'].fillna(0)
                # df1['BOT_COAT_WT'].fillna(0)
                df1 = self.__step_1_1(df1=df1)

                success, df2 = self.__step_2()
                if success is False:
                    return code

                success, df3 = self.__step_3(p_df1=df1, p_df2=df2)
                if success is False:
                    return code
                df3['MAT_ACT_LEN'].fillna(value=0, inplace=True)
                success, df3_new = self.__step_4(p_df3=df3)
                if success is False:
                    return code
                df3_new['MAT_ACT_LEN'].fillna(value=0, inplace=True)
                success, df5 = self.__step_4_1(p_df3_new=df3_new)
                if success is False:
                    return code
                df5 = self.__step_5(p_df5=df5)

                df5_new, df6 = self.__step_5_1(df5=df5)

                success, df54 = self.__step_6(df5_new=df5_new)
                sql = " DELETE FROM BGTARAS1.T_ADS_FACT_SICB_DP0102 " \
                      " WHERE 1=1 " \
                      " AND LEFT(PRODUCE_TIME,14) >= '%s' " \
                      " AND LEFT(PRODUCE_TIME,14) < '%s' " \
                      " AND ACCT = '%s' " \
                      " AND COST_CENTER = '%s' " \
                      " AND UNIT_CODE='%s' " \
                      " AND DATA_TYPE = '%s'" % (
                          self.account_period_start, self.account_period_end, self.account, self.cost_center, self.unit,
                          self.data_type)
                self.logger.info(sql)
                self.db_conn_rds.execute(sql)
                if success is False:
                    df9, success = self.__step_7(df2=df2, df6=df6, p_app_throw_ai_mode=p_app_throw_ai_mode)
                    if success is False:
                        return code

                    # 写库
                    # 数据库dbprod7
                    # 将df54写入到BGRAGGCB.SU_AJBG_DP0102
                    # 将df9写入到BGRAGGCB.SU_AJBG_DP0102
                    df9['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
                    # df9['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
                    df9['TOP_COAT_WT'].fillna(value=0, inplace=True)
                    df9['BOT_COAT_WT'].fillna(value=0, inplace=True)
                    df9.drop(['REC_ID'], axis=1, inplace=True)
                    df9.drop(['TOP_PLATE_WT'], axis=1, inplace=True)
                    df9.drop(['BOT_PLATE_WT'], axis=1, inplace=True)
                    df9.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
                    df9.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
                    df9.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
                    df9.rename(columns={'TEAM': 'CLASS'}, inplace=True)
                    df9.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
                    df9.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
                    df9.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
                    df9.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
                    df9.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
                    df9.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
                    df9.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
                    df9.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
                    df9.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
                    df9.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
                    df9.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
                    df9.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
                    df9.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
                    df9.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
                    df9.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
                    df9.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
                    df9.rename(columns={'TRIM_WIDTH': 'TRIMMING_AMT'}, inplace=True)
                    df9.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
                    df9.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
                    df9.rename(columns={'SORT_GRADE_CODE': 'SORT_GRADE_CODE'}, inplace=True)
                    df9.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
                    df9.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)
                    df9.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
                    df9.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)

                    XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0102',
                                   p_schema='BGTARAS1',
                                   p_dataframe=df9,
                                   p_max_times=5).redo()
                    code = 1
                else:
                    df54.drop(['TOP_PLATE_WT'], axis=1, inplace=True)
                    df54.drop(['BOT_PLATE_WT'], axis=1, inplace=True)
                    code = self.__step_8(df54=df54)

                    # NOTE
                    df9, success = self.__step_7(df2=df2, df6=df6, p_app_throw_ai_mode=p_app_throw_ai_mode)
                    if success is False:
                        return code

                    # 写库
                    # 数据库dbprod7
                    # 将df54写入到BGRAGGCB.SU_AJBG_DP0102
                    # 将df9写入到BGRAGGCB.SU_AJBG_DP0102
                    df9['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
                    # df9['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
                    df9['TOP_COAT_WT'].fillna(value=0, inplace=True)
                    df9['BOT_COAT_WT'].fillna(value=0, inplace=True)
                    df9.drop(['REC_ID'], axis=1, inplace=True)
                    df9.drop(['TOP_PLATE_WT'], axis=1, inplace=True)
                    df9.drop(['BOT_PLATE_WT'], axis=1, inplace=True)
                    df9.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
                    df9.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
                    df9.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
                    df9.rename(columns={'TEAM': 'CLASS'}, inplace=True)
                    df9.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
                    df9.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
                    df9.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
                    df9.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
                    df9.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
                    df9.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
                    df9.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
                    df9.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
                    df9.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
                    df9.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
                    df9.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
                    df9.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
                    df9.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
                    df9.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
                    df9.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
                    df9.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
                    df9.rename(columns={'TRIM_WIDTH': 'TRIMMING_AMT'}, inplace=True)
                    df9.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
                    df9.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
                    df9.rename(columns={'SORT_GRADE_CODE': 'SORT_GRADE_CODE'}, inplace=True)
                    df9.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
                    df9.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)
                    df9.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
                    df9.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)

                    XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0102',
                                   p_schema='BGTARAS1',
                                   p_dataframe=df9,
                                   p_max_times=5).redo()
                    code = 1


                return code
            else:
                success, df2 = self.__step_2()
                if success is False:
                    return code
                sql = "select FROM,TO,DATE,SHIFT,TURN AS TEAM from bgrasids.BASE_SU_J003"
                df6 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
                df6.columns = df6.columns.str.upper()

                df9, success = self.__step_7(df2=df2, df6=df6, p_app_throw_ai_mode=p_app_throw_ai_mode)
                if success is False:
                    return code

                sql = " DELETE FROM BGTARAS1.T_ADS_FACT_SICB_DP0102 " \
                      " WHERE 1=1 " \
                      " AND LEFT(PRODUCE_TIME,14) >= '%s' " \
                      " AND LEFT(PRODUCE_TIME,14) < '%s' " \
                      " AND ACCT = '%s' " \
                      " AND COST_CENTER = '%s' " \
                      " AND UNIT_CODE='%s' " \
                      " AND DATA_TYPE = '%s'" % (
                          self.account_period_start, self.account_period_end, self.account, self.cost_center, self.unit,
                          self.data_type)
                self.logger.info(sql)
                self.db_conn_rds.execute(sql)

                # 写库
                # 数据库dbprod7
                # 将df54写入到BGRAGGCB.SU_AJBG_DP0102
                # 将df9写入到BGRAGGCB.SU_AJBG_DP0102
                df9['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
                # df9['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
                df9['TOP_COAT_WT'].fillna(value=0, inplace=True)
                df9['BOT_COAT_WT'].fillna(value=0, inplace=True)
                df9.drop(['REC_ID'], axis=1, inplace=True)
                df9.drop(['TOP_PLATE_WT'], axis=1, inplace=True)
                df9.drop(['BOT_PLATE_WT'], axis=1, inplace=True)
                df9.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
                df9.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
                df9.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
                df9.rename(columns={'TEAM': 'CLASS'}, inplace=True)
                df9.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
                df9.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
                df9.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
                df9.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
                df9.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
                df9.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
                df9.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
                df9.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
                df9.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
                df9.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
                df9.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
                df9.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
                df9.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
                df9.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
                df9.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
                df9.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
                df9.rename(columns={'TRIM_WIDTH': 'TRIMMING_AMT'}, inplace=True)
                df9.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
                df9.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
                df9.rename(columns={'SORT_GRADE_CODE': 'SORT_GRADE_CODE'}, inplace=True)
                df9.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
                df9.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)
                df9.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
                df9.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)

                XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0102', p_schema='BGTARAS1',
                               p_dataframe=df9,
                               p_max_times=5).redo()
                code = 1

        return code
        # super(DP0102Job, self).do_execute()












        # NOTE 子步骤1 从db_conn_dbprod7的BGRAGGCB.TACAIIQB内取指定时间段所有卷的信息


        # 如果UNIT是Q161，就走现在的逻辑，去调用接口查MAT_ACT_WT;否则直接在df1和df7的sql里直接加一行，取出MAT_ACT_WT
        #df1 = self.__step_1_1(df1=df1)

    def __select_no_pn_rolled_steel(self, p_db_conn_dbprod7=None, p_app_throw_ai_mode=None, p_db_conn_mpp=None):
        # 取not like %PN%的数据
        # dbprod7数据库
        # 如果UNIT是Q161，就走现在的逻辑，去调用接口查MAT_ACT_WT;否则直接在df1和df7的sql里直接加一行，取出MAT_ACT_WT
        additional_property = '' if self.unit == 'Q161' else 'MAT_ACT_WT ,'
        #additional_source_table = 'BGRAGGCB.TACAIIQB'
        if self.data_type == '0':
            additional_source_table = 'TMSIJ4.TTMSIJ495'
        #if self.data_type == 'D':
        #    additional_source_table = 'BGRAGGCB.SU_JHBG_DMAMI1'
        if self.data_type == 'D':
            additional_source_table = 'M1_JH.SU_JHBG_DMAMI1'
        #if self.data_type == 'M':
        #    additional_source_table = 'M1_JH.SU_JHBG_DMAMI1'

        sql = " SELECT " \
              " HEX((RAND()))||TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_ID," \
              " ACCOUNT," \
              " LEFT(TRIM(COST_CENTER),2) AS FACTORY," \
              " COST_CENTER ," \
              " %s" \
              " '%s' AS UNIT, " \
              " TRIM(app_throw_ai_date||app_throw_ai_t) AS WORK_TIME," \
              " TRIM(app_throw_ai_date||app_throw_ai_t) AS PROCESS_START_TIME," \
              " TRIM(app_throw_ai_date||app_throw_ai_t) AS PROCESS_END_TIME," \
              " app_throw_ai_date," \
              " app_throw_ai_t," \
              " PRODUCT_CODE ," \
              " ST_NO ," \
              " MAT_NO," \
              " DEVO_PRODUCT_CODE AS IN_PRODUCT_CODE," \
              " IN_MAT_NO ," \
              " APP_THROW_AI_MODE," \
              " DESIGN_ANNEAL_DIAGRAM_CODE," \
              " TRIM_FLAG," \
              " MAT_ACT_WIDTH," \
              " MAT_ACT_THICK," \
              " IN_MAT_WIDTH," \
              " IN_MAT_THICK," \
              " PLAN_NO," \
              " TRIM_WIDTH," \
              " IN_MAT_INNER_DIA," \
              " CUST_ORDER_NO," \
              " PICKL_TRIM_FLAG," \
              " SORT_GRADE_CODE," \
              " LAYER_TYPE," \
              " TOP_COAT_WT," \
              " BOT_COAT_WT," \
              " LAS_NOTCH_FLAG," \
              " COALESCE(IN_MAT_WT_AI,0) AS IN_MAT_WT_AI," \
              " 'BGRAGGCB' AS REC_REVISOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME," \
              " 'BGRAGGCB' AS REC_CREATOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME" \
              " FROM %s" \
              " WHERE 1=1 " \
              " AND ACCOUNT = '%s'" \
              " AND COST_CENTER = '%s'" \
              " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)>='%s'" \
              " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)<'%s'" \
              " AND APP_THROW_AI_MODE NOT LIKE '%s'" \
              " AND ACCOUNT_TITLE_ITEM='01'" % (additional_property,
                                                self.unit, additional_source_table,
                                                self.account,
                                                self.cost_center,
                                                self.account_period_start,
                                                self.account_period_end,
                                                p_app_throw_ai_mode)
        self.logger.info(sql)
        if self.data_type == '0':
            df = XRetryableQuery(p_db_conn=p_db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        if self.data_type == 'D':
            #df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            df = XRetryableQuery(p_db_conn=p_db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        #if self.data_type == 'M':
            #df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        #    df = XRetryableQuery(p_db_conn=p_db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        #df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        #df.columns = df.columns.str.upper()
        self.logger.info(df)
        success = df.empty is False

        return success, df

    def __step_8(self, df54=None):

        # NOTE 子步骤8 增加删库写库
        # 删库 数据库dbprod7

        #df54['IN_MAT_INNER_DIA'].fillna(0.0)
        #df54['TOP_COAT_WT'].fillna(0.0)
        #df54['BOT_COAT_WT'].fillna(0.0)
        df54['TOP_COAT_WT'] = df54['BOT_COAT_WT'].astype(float)
        df54['BOT_COAT_WT'] = df54['BOT_COAT_WT'].astype(float)
        df54.drop(['REC_ID'], axis=1, inplace=True)
        #df54.drop(['TOP_PLATE_WT'], axis=1, inplace=True)
        #df54.drop(['BOT_PLATE_WT'], axis=1, inplace=True)
        df54.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
        df54.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
        df54.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
        df54.rename(columns={'TEAM': 'CLASS'}, inplace=True)
        df54.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
        df54.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
        df54.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
        df54.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
        df54.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
        df54.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
        df54.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
        df54.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
        df54.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
        df54.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
        df54.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
        df54.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
        df54.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
        df54.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
        df54.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
        df54.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
        df54.rename(columns={'TRIM_WIDTH': 'TRIMMING_AMT'}, inplace=True)
        df54.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
        df54.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
        df54.rename(columns={'SORT_GRADE_CODE': 'SORT_GRADE_CODE'}, inplace=True)
        df54.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
        df54.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)
        df54.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
        df54.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)



        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0102', p_schema='BGTARAS1',
                       p_dataframe=df54,
                       p_max_times=5).redo()
        code = 1

        return code

    def __step_7(self, df2=None, df6=None, p_app_throw_ai_mode=None):
        # NOTE 子步骤7
        # 取not like %PN%的数据 dbprod7数据库
        success, df7 = self.__select_no_pn_rolled_steel(p_db_conn_dbprod7=self.db_conn_dbprod7,
                                                        p_app_throw_ai_mode=p_app_throw_ai_mode, p_db_conn_mpp=self.db_conn_mpp)
        if success is False:
            return None, False
        #origin_rec_id = df7.iloc[0]['REC_ID']
        #origin_work_time = df7.iloc[0]['WORK_TIME']
        df7.columns = df7.columns.str.upper()
        if self.unit == 'Q161':
            df7['MAT_ACT_WT'] = -1
            for index, row in df7.iterrows():
                #v = self.__request_mat_wt_by(p_mat_no=row['MAT_NO'])
                success, v = RequestMatWTAPI(p_mat_no=row['MAT_NO']).request()
                df7.loc[index, 'MAT_ACT_WT'] = v
        df8 = pd.merge(df7, df2, on=['UNIT', 'MAT_NO'], how='left')
        if df8.empty:
            return None, False
        df8.columns = df8.columns.str.upper()
        df8['MAT_ACT_LEN'].fillna(value=0, inplace=True)

        self.logger.info(df8)

        #self.generate_excel(p_dataframe=df8, p_file_name="df8---groupby-before.xls")
        v = ['ACCOUNT',
             'FACTORY',
             'COST_CENTER',
             'UNIT',
             'APP_THROW_AI_DATE',
             'APP_THROW_AI_T',
             'PRODUCT_CODE',
             'ST_NO',
             'MAT_NO',
             'IN_PRODUCT_CODE',
             'IN_MAT_NO',
             'APP_THROW_AI_MODE',
             'DESIGN_ANNEAL_DIAGRAM_CODE',
             'TRIM_FLAG',
             'MAT_ACT_WIDTH',
             'MAT_ACT_THICK',
             'IN_MAT_WIDTH',
             'IN_MAT_THICK',
             'PLAN_NO',
             'TRIM_WIDTH',
             'IN_MAT_INNER_DIA',
             'CUST_ORDER_NO',
             'PICKL_TRIM_FLAG',
             'SORT_GRADE_CODE',
             'LAYER_TYPE',
             'TOP_COAT_WT',
             'BOT_COAT_WT',
             'LAS_NOTCH_FLAG']
        df8['ACCOUNT'].fillna(value='', inplace=True)
        df8['FACTORY'].fillna(value='', inplace=True)
        df8['COST_CENTER'].fillna(value='', inplace=True)
        df8['UNIT'].fillna(value='', inplace=True)
        df8['APP_THROW_AI_DATE'].fillna(value='', inplace=True)
        df8['APP_THROW_AI_T'].fillna(value='', inplace=True)
        df8['PRODUCT_CODE'].fillna(value='', inplace=True)
        df8['ST_NO'].fillna(value='', inplace=True)
        df8['MAT_NO'].fillna(value='', inplace=True)
        df8['IN_PRODUCT_CODE'].fillna(value='', inplace=True)
        df8['IN_MAT_NO'].fillna(value='', inplace=True)
        df8['APP_THROW_AI_MODE'].fillna(value='', inplace=True)
        df8['DESIGN_ANNEAL_DIAGRAM_CODE'].fillna(value='', inplace=True)
        df8['TRIM_FLAG'].fillna(value='', inplace=True)
        df8['MAT_ACT_WIDTH'].fillna(value=0, inplace=True)
        df8['MAT_ACT_THICK'].fillna(value=0, inplace=True)
        df8['IN_MAT_WIDTH'].fillna(value=0, inplace=True)
        df8['IN_MAT_THICK'].fillna(value=0, inplace=True)
        df8['PLAN_NO'].fillna(value='', inplace=True)
        df8['TRIM_WIDTH'].fillna(value=0, inplace=True)
        df8['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
        df8['CUST_ORDER_NO'].fillna(value='', inplace=True)
        df8['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
        df8['SORT_GRADE_CODE'].fillna(value='', inplace=True)
        df8['LAYER_TYPE'].fillna(value='', inplace=True)
        df8['TOP_COAT_WT'].fillna(value=0, inplace=True)
        df8['BOT_COAT_WT'].fillna(value=0, inplace=True)
        df8['LAS_NOTCH_FLAG'].fillna(value='', inplace=True)



        df8['MAT_ACT_WT'].fillna(value=0, inplace=True)
        df8['IN_MAT_WT_AI'].fillna(value=0, inplace=True)

        # df8['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
        # df8['LAYER_TYPE'].fillna(value='', inplace=True)
        # df8['PLAN_NO'].fillna(value='', inplace=True)
        # df8['SORT_GRADE_CODE'].fillna(value='', inplace=True)
        # df8['LAS_NOTCH_FLAG'].fillna(value='', inplace=True)
        # df8['TRIM_FLAG'].fillna(value='', inplace=True)
        # df8['DESIGN_ANNEAL_DIAGRAM_CODE'].fillna(value='', inplace=True)
        # df8['TOP_COAT_WT'].fillna(value=0, inplace=True)
        # df8['BOT_COAT_WT'].fillna(value=0, inplace=True)
        # df8['CUST_ORDER_NO'].fillna(value='', inplace=True)

        #df8['MAT_ACT_WT'].fillna(0)
        #df8['IN_MAT_WT_AI'].fillna(0)
        # 得到SUM(MAT_ACT_WT) AS WT,SUM(IN_MAT_WT_AI) AS IN_WT
        a = df8.groupby(v)['MAT_ACT_WT'].agg([np.sum]).round(2)
        a.rename(columns={'sum': 'WT'}, inplace=True)
        b = df8.groupby(v)['IN_MAT_WT_AI'].agg([np.sum]).round(2)
        b.rename(columns={'sum': 'IN_WT'}, inplace=True)
        c = pd.merge(a, b, on=v, how='left')
        c['ACT_WT'] = c['WT'] - c['IN_WT']

        df8.drop_duplicates(subset=v, keep='first', inplace=True)
        df8_new = pd.merge(c, df8, on=v, how='left')
        #self.generate_excel(p_dataframe=df8_new, p_file_name="df8_new.xls")
        if df8_new.empty:
            return None, False

        df8_new['ACT_IN_WT'] = 0
        df8_new['TOP_PLATE_WT'] = 0
        df8_new['BOT_PLATE_WT'] = 0
        #df8_new['MAT_ACT_LEN'] = 0
        df8_new['MAT_ACT_AREA'] = 0
        df8_new['DATA_TYPE'] = self.data_type

        #def __cal_FROM(x):
            #return self.__cal_FROM(x)
        def __cal_FROM(x):
            # NOTE 距离最近的08:00或20:00
            t = datetime.datetime.strptime(str(x.WORK_TIME), '%Y%m%tmp_dict%H%M%S')
            prev_day = t - datetime.timedelta(days=1)
            today_20 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=20).strftime('%Y%m%tmp_dict%H%M%S')
            today_8 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=8).strftime('%Y%m%tmp_dict%H%M%S')
            yestoday_20 = datetime.datetime(year=prev_day.year, month=prev_day.month, day=prev_day.day,
                                            hour=20).strftime('%Y%m%tmp_dict%H%M%S')
            if x.WORK_TIME >= today_20:
                rst = today_20
            elif x.WORK_TIME >= today_8:
                rst = today_8
            else:
                rst = yestoday_20
            return rst

        def __cal_TEAM(x):
            rst = {1: '甲', 2: '乙', 3: '丙', 4: '丁'}[x.TEAM]
            return rst

        def __cal_SHIFT(x):
            rst = {1: '日', 2: '夜'}[x.SHIFT]
            return rst

        df8_new['FROM'] = df8_new.apply(lambda x: __cal_FROM(x), axis=1)
        #self.generate_excel(p_dataframe=df8_new, p_file_name="df8----final.xls")
        df9 = pd.merge(df8_new, df6, on=['FROM'], how='left')

        #def __cal_TEAM(x):
            #return self.__cal_TEAM(x)

        df9['TEAM'] = df9.apply(lambda x: __cal_TEAM(x), axis=1)

        #def __cal_SHIFT(x):
            #return self.__cal_SHIFT(x)

        df9['SHIFT'] = df9.apply(lambda x: __cal_SHIFT(x), axis=1)
        df9.drop(['FROM'], axis=1, inplace=True)
        df9.drop(['TO'], axis=1, inplace=True)
        df9.drop(['DATE'], axis=1, inplace=True)
        df9.drop(['APP_THROW_AI_DATE'], axis=1, inplace=True)
        df9.drop(['APP_THROW_AI_T'], axis=1, inplace=True)
        df9.drop(['IN_MAT_WT_AI'], axis=1, inplace=True)
        df9.drop(['MAT_ACT_WT'], axis=1, inplace=True)
        #self.generate_excel(p_dataframe=df9, p_file_name="df9.xls")
        success = df9.empty is False
        return df9, success

    def __step_6(self, df5_new=None):
        #df5_new['WT'].fillna(0)
        df5_new['WT'].fillna(value=0, inplace=True)

        v = ['ACCOUNT', 'FACTORY', 'COST_CENTER', 'PRODUCT_CODE', 'IN_MAT_NO']
        df5_new['ACCOUNT'].fillna(value='', inplace=True)
        df5_new['FACTORY'].fillna(value='', inplace=True)
        df5_new['COST_CENTER'].fillna(value='', inplace=True)
        df5_new['PRODUCT_CODE'].fillna(value='', inplace=True)
        df5_new['IN_MAT_NO'].fillna(value='', inplace=True)
        df51 = df5_new.groupby(v)['WT'].agg([np.sum]).round(2)
        df51.rename(columns={'sum': 'NEW_TOT_WT'}, inplace=True)

        # 相当于df5多了一列NEW_TOT_WT
        # 然后把df52的TOT_WT删掉，NEW_TOT_WT改名成TOT_WT
        df52 = pd.merge(df5_new, df51, on=v, how='left')
        df52.drop(['TOT_WT'], axis=1, inplace=True)
        df52.rename(columns={'NEW_TOT_WT': 'TOT_WT'}, inplace=True)
        # self.generate_excel(p_dataframe=df52, p_file_name="df52.xls")
        df52['ABS_WT'] = df52['WT'].abs()
        df52['ROW_NUM_TMP'] = df52.groupby(v)['ABS_WT'].rank(ascending=0, method='dense')
        df52.drop(['ABS_WT'], axis=1, inplace=True)

        # self.generate_excel(p_dataframe=df52, p_file_name="df52.xls")
        def __cal_IN_WT_TMP(x):
            rst = 0
            if x.TOT_WT != 0:
                rst = round(x.IN_WT * x.WT / x.TOT_WT, 3)
            return rst

        df52['IN_WT_TMP'] = df52.apply(lambda x: __cal_IN_WT_TMP(x), axis=1)
        #self.generate_excel(p_dataframe=df52, p_file_name="df52.xls")
        #df52['IN_WT_TMP'].fillna(0)
        df52['IN_WT_TMP'].fillna(value=0, inplace=True)
        v = ['ACCOUNT', 'FACTORY', 'COST_CENTER', 'PRODUCT_CODE', 'IN_MAT_NO']
        df52['ACCOUNT'].fillna(value='', inplace=True)
        df52['FACTORY'].fillna(value='', inplace=True)
        df52['COST_CENTER'].fillna(value='', inplace=True)
        df52['PRODUCT_CODE'].fillna(value='', inplace=True)
        df52['IN_MAT_NO'].fillna(value='', inplace=True)
        df53 = df52.groupby(v)['IN_WT_TMP'].agg([np.sum]).round(2)
        df53.rename(columns={'sum': 'NEW_TOT_IN_WT_TMP'}, inplace=True)
        df54 = pd.merge(df52, df53, on=v, how='left')
        df54.drop(['TOT_IN_WT_TMP'], axis=1, inplace=True)
        df54.rename(columns={'NEW_TOT_IN_WT_TMP': 'TOT_IN_WT_TMP'}, inplace=True)
        # NOTE 但我们要对df54进行筛选，现在df54的work_time的范围是20210225160000-20210226220000。
        # NOTE 但是我们只要保留20210225220000-202102260000这一部分的数据，多的那六个小时不要
        # NOTE 那六个小时只是帮助我们确保边界条件时数据准确用的，但我们不需要把那六个小时的数据写库
        df54 = df54[(df54['WORK_TIME'] >= self.account_period_start) & (df54['WORK_TIME'] < self.account_period_end)]
        success = df54.empty is False
        if success is False:
            return False, df54
        # self.generate_excel(p_dataframe=df54, p_file_name="df54.xls")
        def __cal_ACT_IN_WT(x):
            rst = 0
            if x.ROW_NUM_TMP == 1:
                rst = x.IN_WT_TMP + x.IN_WT - x.TOT_IN_WT_TMP
            if x.ROW_NUM_TMP != 1:
                rst = x.IN_WT_TMP
            return rst

        df54['ACT_IN_WT'] = df54.apply(lambda x: __cal_ACT_IN_WT(x), axis=1)
        df54.drop(['APP_THROW_AI_DATE'], axis=1, inplace=True)
        df54.drop(['APP_THROW_AI_T'], axis=1, inplace=True)
        df54.drop(['IN_MAT_WT_AI'], axis=1, inplace=True)
        df54.drop(['MAT_ACT_WT'], axis=1, inplace=True)
        df54.drop(['IN_WT_TMP'], axis=1, inplace=True)
        df54.drop(['ROW_NUM_TMP'], axis=1, inplace=True)
        df54.drop(['TOT_WT'], axis=1, inplace=True)
        df54.drop(['TOT_IN_WT_TMP'], axis=1, inplace=True)
        df54['PROCESS_START_TIME'] = df54['WORK_TIME']
        df54['PROCESS_END_TIME'] = df54['WORK_TIME']
        df54['ACT_WT'] = df54['WT']
        df54['DATA_TYPE'] = self.data_type

        #self.generate_excel(p_dataframe=df54, p_file_name="df54.xls")
        return success, df54

    def __step_5_1(self, df5=None):
        sql = "select FROM,TO,DATE,SHIFT,TURN AS TEAM from bgrasids.BASE_SU_J003"
        df6 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df6.columns = df6.columns.str.upper()
        #self.generate_excel(p_dataframe=df6, p_file_name="df6.xls")
        df5_new = pd.merge(df5, df6, on=['FROM'], how='left')

        # 班次列名为team, 需要把1234 转换为甲乙丙丁 。
        # 班别列名为shift， 需要把12，要转成夜日。
        #def __cal_TEAM(x):
            #return self.__cal_TEAM(x)

        #df5_new['TEAM'] = df5_new.apply(lambda x: __cal_TEAM(x), axis=1)

        #def __cal_SHIFT(x):
            #return self.__cal_SHIFT(x)
        def __cal_TEAM(x):
            rst = {1: '甲', 2: '乙', 3: '丙', 4: '丁'}[x.TEAM]
            return rst

        def __cal_SHIFT(x):
            rst = {1: '夜', 2: '日'}[x.SHIFT]
            return rst

        df5_new['TEAM'] = df5_new.apply(lambda x: __cal_TEAM(x), axis=1)

        df5_new['SHIFT'] = df5_new.apply(lambda x: __cal_SHIFT(x), axis=1)
        df5_new.drop(['FROM'], axis=1, inplace=True)
        df5_new.drop(['TO'], axis=1, inplace=True)
        df5_new.drop(['DATE'], axis=1, inplace=True)

        #self.generate_excel(p_dataframe=df5_new, p_file_name="df5_new.xls")
        return df5_new, df6

    def __step_5(self, p_df5=None):
        p_df5['TOT_WT'] = 0
        p_df5['IN_WT_TMP'] = 0
        p_df5['TOT_IN_WT_TMP'] = 0
        p_df5['ROW_NUM_TMP'] = 0
        p_df5['ACT_IN_WT'] = 0
        p_df5['TOP_PLATE_WT'] = 0
        p_df5['BOT_PLATE_WT'] = 0
        #p_df5['MAT_ACT_LEN'] = 0
        p_df5['MAT_ACT_AREA'] = 0

        #def __cal_FROM(self, x):
            #return self.__cal_FROM(x)
        def __cal_FROM(x):
            # NOTE 距离最近的08:00或20:00
            t = datetime.datetime.strptime(str(x.WORK_TIME), '%Y%m%tmp_dict%H%M%S')
            prev_day = t - datetime.timedelta(days=1)
            today_20 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=20).strftime('%Y%m%tmp_dict%H%M%S')
            today_8 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=8).strftime('%Y%m%tmp_dict%H%M%S')
            yestoday_20 = datetime.datetime(year=prev_day.year, month=prev_day.month, day=prev_day.day,
                                            hour=20).strftime('%Y%m%tmp_dict%H%M%S')
            if x.WORK_TIME >= today_20:
                rst = today_20
            elif x.WORK_TIME >= today_8:
                rst = today_8
            else:
                rst = yestoday_20
            return rst

        p_df5['FROM'] = p_df5.apply(lambda x: __cal_FROM(x), axis=1)
        #self.generate_excel(p_dataframe=p_df5, p_file_name="p_df5----final.xls")
        return p_df5

    def __step_4_1(self, p_df3_new=None):
        # 对新的df3进行group by IN_MAT_NO 得到SUM(IN_WT) AS NEW_IN_WT
        # 生成df4，df4包含IN_MAT_NO及NEW_IN_WT
        #p_df3_new['IN_WT'].fillna(0)
        p_df3_new['IN_WT'].fillna(value=0, inplace=True)
        v = ['IN_MAT_NO']
        df4 = p_df3_new.groupby(v)['IN_WT'].agg([np.sum]).round(2)
        df4.rename(columns={'sum': 'NEW_IN_WT'}, inplace=True)
        # 然后df3 left merge df4 on IN_MAT_NO
        # 得到df5
        # 相当于df3多了一列NEW_IN_WT
        # 然后把df5的IN_WT删掉，NEW_IN_WT改名成IN_WT
        df5 = pd.merge(p_df3_new, df4, on=['IN_MAT_NO'], how='left')
        df5.drop(['IN_WT'], axis=1, inplace=True)
        df5.rename(columns={'NEW_IN_WT': 'IN_WT'}, inplace=True)
        #self.generate_excel(p_dataframe=df5, p_file_name="df5.xls")
        success = df5.empty is False
        return success, df5

    def __step_4(self, p_df3=None):
        # NOTE 子步骤4 对df3进行groupby, 得到了新的df3
        v = ['ACCOUNT',
             'FACTORY',
             'COST_CENTER',
             'UNIT',
             'APP_THROW_AI_DATE',
             'APP_THROW_AI_T',
             'PRODUCT_CODE',
             'ST_NO',
             'MAT_NO',
             'IN_PRODUCT_CODE',
             'IN_MAT_NO',
             'APP_THROW_AI_MODE',
             'DESIGN_ANNEAL_DIAGRAM_CODE',
             'TRIM_FLAG',
             'MAT_ACT_WIDTH',
             'MAT_ACT_THICK',
             'IN_MAT_WIDTH',
             'IN_MAT_THICK',
             'PLAN_NO',
             'TRIM_WIDTH',
             'IN_MAT_INNER_DIA',
             'CUST_ORDER_NO',
             'PICKL_TRIM_FLAG',
             'SORT_GRADE_CODE',
             'LAYER_TYPE',
             'TOP_COAT_WT',
             'BOT_COAT_WT',
             'LAS_NOTCH_FLAG']
        #p_df3['MAT_ACT_WT'].fillna(0)
        #p_df3['IN_MAT_WT_AI'].fillna(0)
        #p_df3['IN_MAT_INNER_DIA'].fillna(0)
        p_df3['ACCOUNT'].fillna(value='', inplace=True)
        p_df3['FACTORY'].fillna(value='', inplace=True)
        p_df3['COST_CENTER'].fillna(value='', inplace=True)
        p_df3['UNIT'].fillna(value='', inplace=True)
        p_df3['APP_THROW_AI_DATE'].fillna(value='', inplace=True)
        p_df3['APP_THROW_AI_T'].fillna(value='', inplace=True)
        p_df3['PRODUCT_CODE'].fillna(value='', inplace=True)
        p_df3['ST_NO'].fillna(value='', inplace=True)
        p_df3['MAT_NO'].fillna(value='', inplace=True)
        p_df3['IN_PRODUCT_CODE'].fillna(value='', inplace=True)
        p_df3['IN_MAT_NO'].fillna(value='', inplace=True)
        p_df3['APP_THROW_AI_MODE'].fillna(value='', inplace=True)
        p_df3['DESIGN_ANNEAL_DIAGRAM_CODE'].fillna(value='', inplace=True)
        p_df3['TRIM_FLAG'].fillna(value='', inplace=True)
        p_df3['MAT_ACT_WIDTH'].fillna(value=0, inplace=True)
        p_df3['MAT_ACT_THICK'].fillna(value=0, inplace=True)
        p_df3['IN_MAT_WIDTH'].fillna(value=0, inplace=True)
        p_df3['IN_MAT_THICK'].fillna(value=0, inplace=True)
        p_df3['PLAN_NO'].fillna(value='', inplace=True)
        p_df3['TRIM_WIDTH'].fillna(value=0, inplace=True)
        p_df3['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
        p_df3['CUST_ORDER_NO'].fillna(value='', inplace=True)
        p_df3['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)
        p_df3['SORT_GRADE_CODE'].fillna(value='', inplace=True)
        p_df3['LAYER_TYPE'].fillna(value='', inplace=True)
        p_df3['TOP_COAT_WT'].fillna(value=0, inplace=True)
        p_df3['BOT_COAT_WT'].fillna(value=0, inplace=True)
        p_df3['LAS_NOTCH_FLAG'].fillna(value='', inplace=True)

        p_df3['MAT_ACT_WT'].fillna(value=0, inplace=True)
        p_df3['IN_MAT_WT_AI'].fillna(value=0, inplace=True)
        # p_df3['IN_MAT_INNER_DIA'].fillna(value=0, inplace=True)
        # p_df3['LAYER_TYPE'].fillna(value='', inplace=True)
        # p_df3['PLAN_NO'].fillna(value='', inplace=True)
        # p_df3['SORT_GRADE_CODE'].fillna(value='', inplace=True)
        # p_df3['LAS_NOTCH_FLAG'].fillna(value='', inplace=True)
        # p_df3['TRIM_FLAG'].fillna(value='', inplace=True)
        # p_df3['DESIGN_ANNEAL_DIAGRAM_CODE'].fillna(value='', inplace=True)
        # p_df3['PICKL_TRIM_FLAG'].fillna(value='', inplace=True)


        #p_df3 = p_df3.fillna(value=0)

        # 得到SUM(MAT_ACT_WT) AS WT,SUM(IN_MAT_WT_AI) AS IN_WT
        #self.generate_excel(p_dataframe=p_df3, p_file_name="p_df3d.xls")
        a = p_df3.groupby(v)['MAT_ACT_WT'].agg([np.sum]).round(2)
        a.rename(columns={'sum': 'WT'}, inplace=True)
        b = p_df3.groupby(v)['IN_MAT_WT_AI'].agg([np.sum]).round(2)
        b.rename(columns={'sum': 'IN_WT'}, inplace=True)
        c = pd.merge(a, b, on=v, how='left')
        p_df3.drop_duplicates(subset=v, keep='first', inplace=True)
        df3_new = pd.merge(c, p_df3, on=v, how='left')
        #self.generate_excel(p_dataframe=df3_new, p_file_name="df3_new.xls")
        success = df3_new.empty is False
        return success, df3_new

    def __step_3(self, p_df1=None, p_df2=None):
        """
        将步骤1的df1，与步骤2的df2进行merge
        left
        on UNIT,MAT_NO
        得到df3
        """
        df = pd.merge(p_df1, p_df2, on=['UNIT', 'MAT_NO'], how='left')
        df.columns = df.columns.str.upper()
        # df.rename(columns={'ACCOUNT_X': 'ACCOUNT'}, inplace=True)
        # df.drop(['ACCOUNT_Y'], axis=1, inplace=True)
        self.logger.info(df)
        #self.generate_excel(p_dataframe=df, p_file_name="df3---groupby-before.xls")
        success = df.empty is False
        return success, df

    def __step_2(self):
        # NOTE 子步骤2 从db_conn_db7的MMSIJ4.TMMSIJ402内取MAT_ACT_LEN与MAT_NO的对应关系信息
        sql = " select " \
              " OUT_MAT_ACT_LEN AS MAT_ACT_LEN, " \
              " UNIT_CODE as UNIT, " \
              " OUT_MAT_NO as MAT_NO " \
              " FROM " \
              " MMSIJ4.TMMSIJ402 " \
              " WHERE 1=1 " \
              " AND UNIT_CODE='%s' " % (self.unit)
        self.logger.info(sql)
        df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df.columns = df.columns.str.upper()
        success = df.empty is False
        return success, df

    def __step_1_1(self, df1=None):
        if self.unit == 'Q161' and df1.empty is False:
            # NOTE 子步骤1.1
            # NOTE 调用接口 将df1补全实际的出口重量，而不是理论出口重量
            # NOTE URL：http://190.2.245.29/BGM2M1/api/CallCpp
            # 是df1中的MAT_NO，循环查询出每个卷的接口输出的一大堆中的MAT_WT这个值，将其作为MAT_ACT_WT拼接在df1的后面
            df1['MAT_ACT_WT'] = 0
            for index, row in df1.iterrows():
                # v = self.__request_mat_wt_by(p_mat_no=row['MAT_NO'])
                success, v = RequestMatWTAPI(p_mat_no=row['MAT_NO']).request()
                df1.loc[index, 'MAT_ACT_WT'] = v
        return df1


    def __step_1(self, p_db_conn_dbprod7=None, p_app_throw_ai_mode=None, p_db_conn_mpp=None,):
        """
        从db_conn_dbprod7的BGRAGGCB.TACAIIQB内取指定时间段所有卷的信息
        :return:
        """
        # NOTE 倒退6个小时
        t = datetime.datetime.strptime(self.account_period_start, '%Y%m%tmp_dict%H%M%S')
        for i in range(6):
            t -= datetime.timedelta(hours=1)
        v_account_period_start = t.strftime('%Y%m%tmp_dict%H%M%S')

        # 如果UNIT是Q161，就走现在的逻辑，去调用接口查MAT_ACT_WT;否则直接在df1和df7的sql里直接加一行，取出MAT_ACT_WT
        additional_property = '' if self.unit == 'Q161' else 'MAT_ACT_WT,'
        #additional_source_table = 'BGRAGGCB.TACAIIQB'
        if self.data_type == '0':
            additional_source_table = 'TMSIJ4.TTMSIJ495'
        #if self.data_type == 'D':
        #    additional_source_table = 'BGRAGGCB.SU_JHBG_DMAMI1'
        if self.data_type == 'D':
            additional_source_table = 'M1_JH.SU_JHBG_DMAMI1'
        #if self.data_type == 'M':
        #    additional_source_table = 'M1_JH.SU_JHBG_DMAMI1'
        sql = "SELECT " \
              " HEX(RAND())||TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_ID, " \
              " ACCOUNT, " \
              " LEFT(TRIM(COST_CENTER),2) AS FACTORY, " \
              " %s" \
              " '%s' AS UNIT, " \
              " COST_CENTER, " \
              " TRIM(app_throw_ai_date||app_throw_ai_t) AS WORK_TIME, " \
              " app_throw_ai_date, " \
              " app_throw_ai_t, " \
              " PRODUCT_CODE, " \
              " ST_NO, " \
              " MAT_NO, " \
              " DEVO_PRODUCT_CODE AS IN_PRODUCT_CODE, " \
              " IN_MAT_NO, " \
              " APP_THROW_AI_MODE, " \
              " DESIGN_ANNEAL_DIAGRAM_CODE, " \
              " TRIM_FLAG, " \
              " MAT_ACT_WIDTH, " \
              " MAT_ACT_THICK, " \
              " IN_MAT_WIDTH, " \
              " IN_MAT_THICK, " \
              " PLAN_NO, " \
              " TRIM_WIDTH, " \
              " IN_MAT_INNER_DIA, " \
              " CUST_ORDER_NO, " \
              " PICKL_TRIM_FLAG, " \
              " SORT_GRADE_CODE, " \
              " LAYER_TYPE, " \
              " TOP_COAT_WT, " \
              " BOT_COAT_WT, " \
              " LAS_NOTCH_FLAG, " \
              " 'BGRAGGCB' AS REC_REVISOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME," \
              " 'BGRAGGCB' AS REC_CREATOR," \
              " TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME," \
              " IN_MAT_WT_AI " \
              " FROM %s " \
              " WHERE 1=1 " \
              " AND ACCOUNT='%s' " \
              " AND COST_CENTER='%s' " \
              " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)>='%s'" \
              " AND LEFT(app_throw_ai_date||app_throw_ai_t,14)<'%s'" \
              " AND APP_THROW_AI_MODE LIKE '%s' " \
              " AND MAT_ACT_WT!=0 " \
              " AND ACCOUNT_TITLE_ITEM='01' " % (additional_property,
                                                 self.unit,additional_source_table,
                                                 self.account,
                                                 self.cost_center,
                                                 v_account_period_start,
                                                 self.account_period_end,
                                                 p_app_throw_ai_mode)
        self.logger.info(sql)
        if self.data_type == '0':
            df = XRetryableQuery(p_db_conn=p_db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        if self.data_type == 'D':
            #df = XRetryableQuery(p_db_conn=p_db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
            df = XRetryableQuery(p_db_conn=p_db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        #if self.data_type == 'M':
            #df = XRetryableQuery(p_db_conn=p_db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        #    df = XRetryableQuery(p_db_conn=p_db_conn_mpp, p_sql=sql, p_max_times=5).redo()
        #df = XRetryableQuery(p_db_conn=p_db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        #self.logger.info(df)
        success = df.empty is False

        return success, df

    def __cal_FROM(x):
        # NOTE 距离最近的08:00或20:00
        t = datetime.datetime.strptime(str(x.WORK_TIME), '%Y%m%tmp_dict%H%M%S')
        prev_day = t - datetime.timedelta(days=1)
        today_20 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=20).strftime('%Y%m%tmp_dict%H%M%S')
        today_8 = datetime.datetime(year=t.year, month=t.month, day=t.day, hour=8).strftime('%Y%m%tmp_dict%H%M%S')
        yestoday_20 = datetime.datetime(year=prev_day.year, month=prev_day.month, day=prev_day.day,
                                        hour=20).strftime('%Y%m%tmp_dict%H%M%S')
        if x.WORK_TIME >= today_20:
            rst = today_20
        elif x.WORK_TIME >= today_8:
            rst = today_8
        else:
            rst = yestoday_20
        return rst

    def __cal_TEAM(x):
        rst = {1: '甲', 2: '乙', 3: '丙', 4: '丁'}[x.TEAM]
        return rst

    def __cal_SHIFT(x):
        rst = {1: '日', 2: '夜'}[x.SHIFT]
        return rst





